{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import xlwings as xw\n",
    "import pandas as pd\n",
    "from pandas import DataFrame, Series\n",
    "import re\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2016.11月工资..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2016.12月工资汇总.xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.10月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.1月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.2月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.3月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.4月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.5月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.6月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.7月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.8月工资汇总..xlsx',\n",
       " 'F:\\\\文档\\\\森马\\\\恒金档案袋\\\\恒金工资表201610-201710\\\\2017.9月工资汇总..xlsx']"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = r'F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710'\n",
    "path_lst = [os.path.join(path, i) for i in os.listdir(path)]\n",
    "path_lst"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "app = xw.App(visible=True, add_book=False)\n",
    "# xb = app.books.open(path_lst[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>1</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [1]\n",
       "Index: []"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# init_df = pd.DataFrame(columns=[1])\n",
    "# init_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def getOneTable():\n",
    "    frames = []\n",
    "    for i in range(1,4):\n",
    "        sheet = xb.sheets[i]\n",
    "        one = sheet.range('a2:aa300').options(pd.DataFrame).value\n",
    "        one.dropna(axis=0, how='any', thresh=3, inplace=True)\n",
    "        one.dropna(axis=1, how='all', inplace=True)\n",
    "    #     print(one)\n",
    "        frames.append(one)\n",
    "    init_df = pd.concat(frames, join='outer', axis=0)\n",
    "    return init_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2016.11月工资..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2016.12月工资汇总.xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.10月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.1月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.2月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.3月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.4月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.5月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.6月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.7月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.8月工资汇总..xlsx\n",
      "F:\\文档\\森马\\恒金档案袋\\恒金工资表201610-201710\\2017.9月工资汇总..xlsx\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "Float64Index: 1717 entries, 1.0 to nan\n",
      "Data columns (total 29 columns):\n",
      "保底补贴    3 non-null float64\n",
      "入职时间    1681 non-null datetime64[ns]\n",
      "其他      5 non-null float64\n",
      "其它      27 non-null float64\n",
      "出勤天数    1681 non-null float64\n",
      "加班费     1095 non-null float64\n",
      "劳动纪律    25 non-null float64\n",
      "备注      312 non-null object\n",
      "姓名      1681 non-null object\n",
      "实发工资    1717 non-null float64\n",
      "实际补贴    14 non-null float64\n",
      "工作天数    1716 non-null float64\n",
      "工资      237 non-null float64\n",
      "应发工资    1717 non-null float64\n",
      "房补      502 non-null float64\n",
      "手续费     1717 non-null float64\n",
      "搬运费     57 non-null float64\n",
      "水电费     24 non-null float64\n",
      "社保      1085 non-null float64\n",
      "福利      68 non-null float64\n",
      "组别      1249 non-null object\n",
      "补贴      39 non-null float64\n",
      "计件      1717 non-null float64\n",
      "计件补贴    229 non-null float64\n",
      "计时      78 non-null float64\n",
      "请假天数    244 non-null float64\n",
      "车种      1681 non-null object\n",
      "车费补贴    80 non-null float64\n",
      "饭补      1717 non-null float64\n",
      "dtypes: datetime64[ns](1), float64(24), object(4)\n",
      "memory usage: 402.4+ KB\n"
     ]
    }
   ],
   "source": [
    "all_df = []\n",
    "for x in path_lst:\n",
    "    xb = app.books.open(x)\n",
    "    one = getOneTable()\n",
    "    one['月份'] = x[-17:-10]\n",
    "    all_df.append(one)\n",
    "    xb.close()\n",
    "all_data = pd.concat(all_df, join='outer', axis=0)\n",
    "all_data.info()\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "all_data.to_excel('d:\\\\恒金.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2017.9月'"
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
